package com.sunny.myproject.sales.action;

import java.io.InputStream;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.propertyeditors.CustomDateEditor;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.WebDataBinder;
import org.springframework.web.bind.annotation.InitBinder;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.sunny.myproject.core.base.action.BaseAction;
import com.sunny.myproject.core.base.util.DateUtil;
import com.sunny.myproject.sales.entity.SalesTicket;
import com.sunny.myproject.sales.service.SalesTicketService;
import com.sunny.myproject.sales.util.SalesTicketUitl;

@Controller
@RequestMapping("/sales/salesTicket")
public class SalesTicketAction extends BaseAction<SalesTicket> {

	private Logger logger = Logger.getLogger(SalesTicketAction.class);
	private String title = "销售记录";
	
	@Autowired
	private SalesTicketService service;
	@Autowired
	private SalesTicketUitl salesTicketUitl;

	@Override
	@RequestMapping(params = "list")
	public String list(Model model, HttpServletRequest request,HttpServletResponse response) {
		super.pager = super.pageStart(model, request,response, "sales/salesTicket","salesTicket",title);
		super.pager = super.pageEnd(model, service.num(criterions), pager);
		super.returnValue(model, service.find(criterions,
				super.pager.getFirstResults(), super.pager.getMaxResults()));
		return "sales/salesTicketList";
	}

	@Override
	@RequestMapping(params = "add")
	public String add(HttpServletRequest request, HttpServletResponse response) {
		super.add(title, request);
		return "sales/salesTicketEdit";
	}

	@Override
	@RequestMapping(params = "edit")
	public String edit(Model model, HttpServletRequest request,HttpServletResponse response) {
		super.returnValue(model, service.findById(super.edit(title, model, request)));
		return "sales/salesTicketEdit";
	}

	@Override
	@RequestMapping(params = "delete")
	public String delete(HttpServletRequest request,HttpServletResponse response) {
		service.deleteByIds(super.param2(request));
		return "redirect:/sales/salesTicket?list";
	}

	@Override
	@RequestMapping(params = "save")
	public @ResponseBody
	String save(HttpServletRequest request, HttpServletResponse response,@ModelAttribute SalesTicket t) {
		service.save(t);
		return "sales/salesTicket?list";
	}

	@Override
	@RequestMapping(params = "view")
	public String view(Model model, HttpServletRequest request,HttpServletResponse response) {
		super.returnViewValue(model, service.findById(super.view(title, model, request)));
		return "sales/salesTicketEdit";
	}

	@RequestMapping(params="upload")
	public String upload(){
		return "sales/salesTicketUpload";
	}
	/**
	 * 销售记录上传
	 * @param file
	 * @return
	 */
	@RequestMapping(params = "saveUpload")
	public String saveUpload(@RequestParam("file") MultipartFile file) {
		try {
			InputStream is = file.getInputStream();

			// 创建xls文件对象
			HSSFWorkbook workbook = new HSSFWorkbook(is);
			HSSFSheet sheet = workbook.getSheetAt(0);// 取出第一个工作表
			logger.info("sheet_lastRowNum:"+sheet.getLastRowNum());
			// 开始循环遍历行，表头不处理
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				// 获取行对象
				HSSFRow row = sheet.getRow(i);
				if (row == null) {
					break;
				}
				SalesTicket ticket = new SalesTicket();
				ticket.setP_name(row.getCell(0).getStringCellValue());
				ticket.setP_type(row.getCell(1).getStringCellValue());
				ticket.setSaler(row.getCell(2).getStringCellValue());
				ticket.setShop(row.getCell(3).getStringCellValue());
				ticket.setPrice(row.getCell(4).getNumericCellValue());
				ticket.setAmount(Integer.parseInt(String.valueOf(
								row.getCell(5).getNumericCellValue()).replace(
								".0", "")));
				ticket.setDate(DateUtil.formateStringDate(DateUtil.formatDateString(new Date())));
				ticket.setProfits(salesTicketUitl.getProfits(ticket.getP_name(),
						ticket.getPrice())*ticket.getAmount());
				service.save(ticket);
				logger.info("name:"+row.getCell(0).getStringCellValue());
				logger.info("i:"+i);
			}
			logger.info("循环结束");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "sales/uploadSuccess";
	}
	
	/**
	 * 进入查询前三页面
	 * @param model
	 * @param request
	 * @param response
	 * @return
	 */
	@RequestMapping(params="queryTop3")
	public String queryTop3(Model model, HttpServletRequest request,
			HttpServletResponse response){
		String status = request.getParameter("status");
		
		StringBuffer hql = new StringBuffer("select saler from SalesTicket group by saler");
		@SuppressWarnings("unchecked")
		List<String> saler_list = (List<String>) service.queryHql(hql.toString());
		
		hql = new StringBuffer("select shop from SalesTicket group by shop");
		@SuppressWarnings("unchecked")
		List<String> shop_list = (List<String>) service.queryHql(hql.toString());
		
		model.addAttribute("saler", saler_list);
		model.addAttribute("shop", shop_list);
		
		if(status.equals("shop")){
			return "sales/queryTop3Shop";
		}else if(status.equals("saler")){
			return "sales/queryTop3Saler";
		}
		return "";
	}
	/**
	 * 销售前三的金额查询
	 * @param type
	 * @param date
	 * @return
	 * @throws ParseException 
	 */
	@RequestMapping(params="queryTop3Price")
	public String queryTop3Price(@RequestParam("type")String type,@RequestParam("date")String date,Model model,HttpServletRequest request) throws ParseException{
		logger.info("type:"+type);
		logger.info("date:"+date);
		//查询的状态
		String status = request.getParameter("status");
		logger.info("status:"+status);
		
		String num = request.getParameter("num");
		logger.info("num:"+num);
		StringBuffer hql =new StringBuffer("SELECT SUM(price),p_name,shop,saler FROM SalesTicket where 1=1 ");
		if(date==null||date.equalsIgnoreCase("")){
			date = DateUtil.formatDateString(new Date());
		}
		
		//销售人员，店铺查询
		String saler = request.getParameter("saler");
		logger.info("saler:"+saler);
		String shop = request.getParameter("shop");
		logger.info("shop:"+shop);
		
		if(saler!=null&&!saler.equalsIgnoreCase(""))
			hql.append(" and saler='"+saler+"'");
		if(shop!=null&&!shop.equalsIgnoreCase(""))
			hql.append(" and shop='"+shop+"'");
		
		
		//日查询
		if(type.equals("0")){
			hql.append(" and date = '"+DateUtil.formatDateString(DateUtil.formateStringDate(date))+"' ");
		}else if(type.equals("1")){
			hql.append(" and date>='"+DateUtil.formatDateString(DateUtil.getFirtDateOfMonth(date))+"' ");
			hql.append(" and date<='"+DateUtil.formatDateString(DateUtil.getEndDateOfMonth(date))+"' ");
		}
		if(saler!=null&&!saler.equalsIgnoreCase("")&&shop!=null&&!shop.equalsIgnoreCase("")){
			hql.append(" GROUP BY p_name ORDER BY price DESC");
		}else{
			if(status!=null&&status.equalsIgnoreCase("saler")){
				hql.append(" GROUP BY p_name,shop,saler ORDER BY price DESC");
			}else{
				hql.append(" GROUP BY p_name,shop ORDER BY price DESC");
			}
		}
		
		@SuppressWarnings("unchecked")
		List<Object[]> list = (List<Object[]>) service.queryHql(hql.toString());
		List<SalesTicket> salesTickets = new ArrayList<SalesTicket>();
		for (Object[] object : list) {
			SalesTicket salesTicket = new SalesTicket();
			salesTicket.setPrice(Double.parseDouble(String.valueOf(object[0])));
			salesTicket.setP_name(String.valueOf(object[1]));
			salesTicket.setShop(String.valueOf(object[2]));
			salesTicket.setSaler(String.valueOf(object[3]));
			if(num!=null){
				if(salesTickets.size()<Integer.parseInt(num))
				salesTickets.add(salesTicket);
			}else{
				if(salesTickets.size()<3)
				salesTickets.add(salesTicket);
			}
		}
		String str = "";
		for (SalesTicket salesTicket : salesTickets) {
			if(status!=null&&status.equalsIgnoreCase("saler")){
				str +="{name:'"+salesTicket.getP_name()+"-"+salesTicket.getShop()+"-,"+salesTicket.getSaler()+"',data: ["+salesTicket.getPrice()+"]},";
			}else{
				str +="{name:'"+salesTicket.getP_name()+"-"+salesTicket.getShop()+"',data: ["+salesTicket.getPrice()+"]},";
			}
		}
		str = str.length()>0?str.substring(0, str.length()-1):"";
		model.addAttribute("series", str);	
		
		this.returnValue(model, salesTickets);
		//设置页面type,date
		model.addAttribute("type", type);
		logger.info("type:"+type);
		model.addAttribute("date", date);
		
		//设置页面saler,shop
		model.addAttribute("saler", saler);
		model.addAttribute("shop", shop);
		
		//设置页面状态,是人员还是店面
		model.addAttribute("status", status);
		return "sales/query3Price";
	}
	
	/**
	 * 销售前三的数量查询
	 * @param type
	 * @param date
	 * @return
	 * @throws ParseException 
	 */
	@RequestMapping(params="queryTop3Amount")
	public String queryTop3Amount(@RequestParam("type")String type,@RequestParam("date")String date,Model model,HttpServletRequest request) throws ParseException{
		logger.info("type:"+type);
		logger.info("date:"+type);
		//查询的状态
		String status = request.getParameter("status");
		logger.info("status:"+status);
		String num = request.getParameter("num");
		logger.info("num:"+num);
		StringBuffer hql =new StringBuffer("SELECT SUM(amount),p_name,shop,saler FROM SalesTicket where 1=1 ");
		if(date==null||date.equalsIgnoreCase("")){
			date = DateUtil.formatDateString(new Date());
		}
		
		//销售人员，店铺查询
		String saler = request.getParameter("saler");
		logger.info("saler:"+saler);
		String shop = request.getParameter("shop");
		logger.info("shop:"+shop);
		
		if(saler!=null&&!saler.equalsIgnoreCase(""))
			hql.append(" and saler='"+saler+"'");
		if(shop!=null&&!shop.equalsIgnoreCase(""))
			hql.append(" and shop='"+shop+"'");
		
		
		//日查询
		if(type.equals("0")){
			hql.append(" and date = '"+DateUtil.formatDateString(DateUtil.formateStringDate(date))+"' ");
		}else if(type.equals("1")){
			hql.append(" and date>='"+DateUtil.formatDateString(DateUtil.getFirtDateOfMonth(date))+"' ");
			hql.append(" and date<='"+DateUtil.formatDateString(DateUtil.getEndDateOfMonth(date))+"' ");
		}
		if(saler!=null&&!saler.equalsIgnoreCase("")&&shop!=null&&!shop.equalsIgnoreCase("")){
			hql.append(" GROUP BY p_name ORDER BY amount DESC");
		}else{
			if(status!=null&&status.equalsIgnoreCase("saler")){
				hql.append(" GROUP BY p_name,shop,saler ORDER BY amount DESC");
			}else{
				hql.append(" GROUP BY p_name,shop ORDER BY amount DESC");
			}
		}

		@SuppressWarnings("unchecked")
		List<Object[]> list = (List<Object[]>) service.queryHql(hql.toString());
		List<SalesTicket> salesTickets = new ArrayList<SalesTicket>();
		for (Object[] object : list) {
			SalesTicket salesTicket = new SalesTicket();
			salesTicket.setAmount(Integer.parseInt(String.valueOf(object[0])));
			salesTicket.setP_name(String.valueOf(object[1]));
			salesTicket.setShop(String.valueOf(object[2]));
			salesTicket.setSaler(String.valueOf(object[3]));
			if(num!=null){
				if(salesTickets.size()<Integer.parseInt(num))
					salesTickets.add(salesTicket);
			}else{
				if(salesTickets.size()<3)
					salesTickets.add(salesTicket);
			}
		}
		String str = "";
		for (SalesTicket salesTicket : salesTickets) {
			if(status!=null&&status.equalsIgnoreCase("saler")){
				str +="{name:'"+salesTicket.getP_name()+"-"+salesTicket.getShop()+"-"+salesTicket.getSaler()+"',data: ["+salesTicket.getAmount()+"]},";
			}else{
				str +="{name:'"+salesTicket.getP_name()+"-"+salesTicket.getShop()+"',data: ["+salesTicket.getAmount()+"]},";
			}
		}
		str = str.length()>0?str.substring(0, str.length()-1):"";
		model.addAttribute("series", str);		
		this.returnValue(model, salesTickets);
		
		//设置页面type,date
		model.addAttribute("type", type);
		logger.info("type:"+type);
		model.addAttribute("date", date);
		
		//设置页面saler,shop
		model.addAttribute("saler", saler);
		model.addAttribute("shop", shop);
		
		//设置页面状态,是人员还是店面
		model.addAttribute("status", status);
		return "sales/query3Amount";
	}
	
	@RequestMapping(params="queryProfits")
	public String queryProfits(Model model, HttpServletRequest request,
			HttpServletResponse response){
		String hql = "select p_name,shop,sum(profits) from SalesTicket group by p_name,shop order by sum(profits) desc";
		@SuppressWarnings("unchecked")
		List<Object[]> objects = (List<Object[]>) service.queryHql(hql);
		String str = "";
		for(int i=0;i<3;i++){
			Object[] obj = objects.get(i);
			str +="{name:'"+obj[0]+"-"+obj[1]+"',data:["+obj[2]+"]},";
		}
		str = str.length()>0?str.substring(0, str.length()-1):"";
		model.addAttribute("series", str);	
		return "sales/salesTicketQueryProfits";
	}
	
	@RequestMapping(params="queryProduce")
	public String queryProduce(Model model, HttpServletRequest request,
			HttpServletResponse response){
		String hql = "select p_name,sum(amount) from SalesTicket group by p_name order by sum(amount) desc";
		@SuppressWarnings("unchecked")
		List<Object[]> objects = (List<Object[]>) service.queryHql(hql);
		String str = "";
		for(int i=0;i<3;i++){
			Object[] obj = objects.get(i);
			str +="{name:'"+obj[0]+"',data:["+obj[1]+"]},";
		}
		str = str.length()>0?str.substring(0, str.length()-1):"";
		model.addAttribute("series", str);	
		return "sales/salesTicketQueryProduce";
	}
	
	@RequestMapping(params="json")
	public @ResponseBody String json(Model model, HttpServletRequest request,
			HttpServletResponse response){
		String hql = "select p_name,sum(amount) from SalesTicket group by p_name order by sum(amount) desc";
		@SuppressWarnings("unchecked")
		List<Object[]> objects = (List<Object[]>) service.queryHql(hql);
		String str = "";
		for(int i=0;i<3;i++){
			Object[] obj = objects.get(i);
			str +="{name:'"+obj[0]+"',data:["+obj[1]+"]},";
		}
		str = str.length()>0?str.substring(0, str.length()-1):"";
		return str;
	}
	
	@InitBinder
	public void initBinder(WebDataBinder binder){
		//注册定义的属性编辑器
		//1.日期
		 DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		 CustomDateEditor dateEditor = new CustomDateEditor(df, true);
		 //表示如果命令对象有date类型的属性,将使用该属性编辑器进行类型转换
		 binder.registerCustomEditor(Date.class, dateEditor);
	}
}
